﻿-- Function: pl_function.guardar_minstal(character varying)

-- DROP FUNCTION pl_function.guardar_minstal(character varying);

CREATE OR REPLACE FUNCTION pl_function.guardar_minstal(p_text character varying)
  RETURNS integer AS
$BODY$
declare
	ids numeric(10, 0) := 0;
	p_cperini character(4);
	p_cperfin character(4);
	p_dpredio character varying(10);
	p_minstal character(10);
	v_idsigma character(10);
	p_vhostnm character varying(25);
	p_vusernm character varying(25);
	p_canocon numeric;
	p_nvalins numeric(10, 5);
begin
	select idsigma, dpredio, vhostnm, vusernm, canocon, nvalins
	into p_minstal, p_dpredio, p_vhostnm, p_vusernm, p_canocon, p_nvalins
	from pl_table.tmp_minstal(p_text);

	if p_minstal = '-1' then
		select coalesce(max(idsigma)::numeric(10, 0), 0) into ids from registro.minstal;
		p_minstal := lpad((ids + 1)::varchar, 10, '0');
	end if;

	with new_values(idsigma, dpredio, cnumpis, canocon,
		ctipins, nvalanc, nvalalt, nvallar,
		ctipint, vdesint, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm
	) as (
	select  idsigma, dpredio, cnumpis, canocon,
		ctipins, nvalanc, nvalalt, nvallar,
		ctipint, vdesint, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm
	from pl_table.tmp_minstal(p_text)
	), upsert as ( 
		update registro.minstal m set
			dpredio=n.dpredio, cnumpis=n.cnumpis, canocon=n.canocon,
			ctipins=n.ctipins, nvalanc=n.nvalanc, nvalalt=n.nvalalt,
			nvallar=n.nvallar, ctipint=n.ctipint, vdesint=n.vdesint,
			cmateri=n.cmateri, cconser=n.cconser, cmurcol=n.cmurcol,
			ctechos=n.ctechos, cmpisos=n.cmpisos, cpueven=n.cpueven,
			crevest=n.crevest, cbanios=n.cbanios, celectr=n.celectr,
			narecom=n.narecom, nporcom=n.nporcom, nestado=n.nestado,
			vhostnm=n.vhostnm, vusernm=n.vusernm, ddatetm=n.ddatetm
		    from new_values n
		    where m.idsigma = n.idsigma
		    returning m.*
	)
	insert into registro.minstal(
		idsigma, dpredio, cnumpis, canocon,
		ctipins, nvalanc, nvalalt, nvallar,
		ctipint, vdesint, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm
	) select
		p_minstal idsigma, dpredio, cnumpis, canocon,
		ctipins, nvalanc, nvalalt, nvallar,
		ctipint, vdesint, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm
	from new_values where not exists(select 1 from upsert where upsert.idsigma=new_values.idsigma);
	
	select c.cperini, rpad(coalesce(trim(c.cperfin), ''), 4, '*') into p_cperini, p_cperfin
	from registro.dpredio b inner join registro.mhresum c on b.mhresum=c.idsigma
	where b.idsigma=p_dpredio;

	if p_cperfin = '****' then
		select max(cperiod) into p_cperfin from public.mperiodo;
	end if;
	
	delete from registro.dinstal where minstal=p_minstal;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids from registro.dinstal;

	insert into registro.dinstal(
		idsigma, minstal, nantigu, nvaluni,
		nincrem, npordep, ndepred, nvalare,
		nvalcom, nvalins, nestado, vhostnm,
		vusernm, ddatetm, cperiod)
	 select
		lpad(((row_number() over(order by a.cperiod)) + ids)::varchar, 10, '0') idsigma,
		p_minstal minstal,
		(a.cperiod::int - p_canocon::int) nantigu,
		0 nvaluni,
		0 nincrem,
		0 npordep,
		0 ndepred,
		0 nvalare,
		0 nvalcom,
		p_nvalins nvalins,
		1 nestado,
		p_vhostnm vhostnm,
		p_vusernm vusernm,
		now() ddatetm,
		a.cperiod
	from public.mperiodo a where a.cperiod::int>=p_cperini::int and a.cperiod::int<=p_cperfin::int and a.cperiod::int>=p_canocon::int;

	select pl_function.guardar_vpredio(p_dpredio, p_vhostnm, p_vusernm) into ids;
	return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.guardar_minstal(character varying)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.guardar_minstal(character varying) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.guardar_minstal(character varying) TO public;
GRANT EXECUTE ON FUNCTION pl_function.guardar_minstal(character varying) TO desarrollo;
